{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d4fc7971",
   "metadata": {},
   "outputs": [],
   "source": [
    "import  esda \n",
    "import  pandas  as  pd \n",
    "import  geopandas  as  gpd \n",
    "from  geopandas  import  GeoDataFrame \n",
    "import  libpysal  as  lps \n",
    "import  numpy  as  np \n",
    "import  matplotlib.pyplot  as  plt \n",
    "from  shapely.geometry  import  Point "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "26a15422",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>corresponding_coordinates</th>\n",
       "      <th>current_address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POINT (114.32187 30.52462)</td>\n",
       "      <td>湖北省武汉市武昌区首义路街道民主路三村20号</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POINT (114.36671 30.53329)</td>\n",
       "      <td>湖北省武汉市武昌区珞珈山街道东湖新村八一路134号</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>POINT (114.38208 30.59952)</td>\n",
       "      <td>湖北省武汉市洪山区和平街道和平社区沙湖港湾B区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>POINT (114.39428 30.51703)</td>\n",
       "      <td>湖北省武汉市洪山区关山街道东方巴黎B-1-501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>POINT (114.35846 30.59482)</td>\n",
       "      <td>湖北省武汉市洪山区和平街道团结大道爱家国际3-1603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50556</th>\n",
       "      <td>POINT (114.04105 30.50985)</td>\n",
       "      <td>湖北省武汉市蔡甸区大集街道杨家众村1组</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50557</th>\n",
       "      <td>POINT (113.93712 30.45975)</td>\n",
       "      <td>湖北省武汉市蔡甸区永安街道朴树村4组</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50558</th>\n",
       "      <td>POINT (114.21781 30.55153)</td>\n",
       "      <td>湖北省武汉市汉阳区五里墩街道桃花岛中信社区302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50559</th>\n",
       "      <td>POINT (114.01812 30.57760)</td>\n",
       "      <td>湖北省武汉市蔡甸区蔡甸街道盛泰苑</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50560</th>\n",
       "      <td>POINT (113.97652 30.40821)</td>\n",
       "      <td>湖北省武汉市蔡甸区桐湖办事处瓦碴堡村刘湾9号</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50561 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        corresponding_coordinates              current_address\n",
       "0      POINT (114.32187 30.52462)       湖北省武汉市武昌区首义路街道民主路三村20号\n",
       "1      POINT (114.36671 30.53329)    湖北省武汉市武昌区珞珈山街道东湖新村八一路134号\n",
       "2      POINT (114.38208 30.59952)      湖北省武汉市洪山区和平街道和平社区沙湖港湾B区\n",
       "3      POINT (114.39428 30.51703)     湖北省武汉市洪山区关山街道东方巴黎B-1-501\n",
       "4      POINT (114.35846 30.59482)  湖北省武汉市洪山区和平街道团结大道爱家国际3-1603\n",
       "...                           ...                          ...\n",
       "50556  POINT (114.04105 30.50985)          湖北省武汉市蔡甸区大集街道杨家众村1组\n",
       "50557  POINT (113.93712 30.45975)           湖北省武汉市蔡甸区永安街道朴树村4组\n",
       "50558  POINT (114.21781 30.55153)     湖北省武汉市汉阳区五里墩街道桃花岛中信社区302\n",
       "50559  POINT (114.01812 30.57760)             湖北省武汉市蔡甸区蔡甸街道盛泰苑\n",
       "50560  POINT (113.97652 30.40821)       湖北省武汉市蔡甸区桐湖办事处瓦碴堡村刘湾9号\n",
       "\n",
       "[50561 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import psycopg2\n",
    "# database，user，password，host，port分别对应要连接的PostgreSQL数据库的数据库名、数据库用户名、用户密码、主机、端口信息\n",
    "conn = psycopg2.connect(database=\"infectious\",user=\"postgres\",password=\"cxy010034\",host=\"127.0.0.1\",port=\"5432\")\n",
    "sql=\"select corresponding_coordinates,current_address from hubei_corresponding where reporting_area='武汉市'\"\n",
    "gdf = gpd.GeoDataFrame.from_postgis(sql, con=conn, geom_col='corresponding_coordinates')#4326\n",
    "gdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce78af22",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
